package com.example.excelapp.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.excelapp.entity.UserData;
import com.example.excelapp.mapper.UserMapper;
import com.example.excelapp.utils.ExcelUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

@Slf4j
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, UserData> implements UserService {

    @Autowired
    private PlatformTransactionManager transactionManager;

    private static final int BATCH_SIZE = 10000;  // 每批处理的数据条数

    /**
     * 生成一百万条数据并插入数据库，且以一万条为一批（数据流向：内存 =》 数据库[excel-app]）
     */
    @Override
    public void generateAndInsertMillionData() {
        log.info("开始生成一百万条数据并插入数据库");
        List<UserData> users = new ArrayList<>(BATCH_SIZE);
        long startTime = System.currentTimeMillis();
        int totalInserted = 0;
        DefaultTransactionDefinition def = new DefaultTransactionDefinition();
        def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);

        // 1. 分批生成和插入一百万条数据
        for (int i = 1; i <= 1000000; i++) {
            UserData user = new UserData();
            user.setUsername("User" + i);
            user.setAge(20 + (i % 50));
            user.setSalary(3000.00 + (i % 1000));
            user.setBirthDate(LocalDate.of(1980 + (i % 40), 1 + (i % 12), 1 + (i % 28)));
            users.add(user);

            // 每当达到 BATCH_SIZE 时，开始事务并提交
            if (i % BATCH_SIZE == 0) {
                TransactionStatus status = transactionManager.getTransaction(def);  // 开始事务
                try {
                    this.saveBatch(users);  // 批量插入
                    transactionManager.commit(status);  // 提交事务
                    totalInserted += users.size();
                    log.info("已插入 {} 条数据, 耗时: {} ms", totalInserted, System.currentTimeMillis() - startTime);
                    users.clear();  // 清空缓存
                } catch (Exception e) {
                    transactionManager.rollback(status);  // 回滚事务
                    log.error("插入失败，已回滚事务", e);
                }
            }
        }

        // 处理最后一批不足 BATCH_SIZE 的数据
        if (!users.isEmpty()) {
            TransactionStatus status = transactionManager.getTransaction(def);  // 开始事务
            try {
                this.saveBatch(users);  // 批量插入
                transactionManager.commit(status);  // 提交事务
                totalInserted += users.size();
                log.info("已插入 {} 条数据, 耗时: {} ms", totalInserted, System.currentTimeMillis() - startTime);
            } catch (Exception e) {
                transactionManager.rollback(status);  // 回滚事务
                log.error("插入失败，已回滚事务", e);
            }
        }

        log.info("一百万条数据插入数据库完成，总耗时: {} ms", System.currentTimeMillis() - startTime);
    }

    /**
     * 导出数据库中的数据到 Excel 文件（数据流向：数据库[excel-app] =》 Excel[users.xlsx]）
     */
    @Override
    public void exportExcel() {
        log.info("开始从数据库导出数据到 Excel 文件");
        // 1. 创建 Excel 文件路径
        File excelDir = new File("src/main/resources/excel");
        if (!excelDir.exists()) {
            excelDir.mkdirs();
        }
        File excelFile = new File(excelDir, "users.xlsx");
        try (FileOutputStream outputStream = new FileOutputStream(excelFile)) {
            // 2. 使用 EasyExcel 构建写入器
            long startTime;
            try (ExcelWriter excelWriter = EasyExcel.write(outputStream, UserData.class).build()) {
                WriteSheet writeSheet = EasyExcel.writerSheet("用户数据").build();
                startTime = System.currentTimeMillis();
                int totalExported = 0;
                int batchCount = 0;
                // 3. 分批从数据库读取数据并写入到 Excel
                for (int i = 0; ; i += BATCH_SIZE) {
                    List<UserData> users = this.lambdaQuery()
                            .select(UserData::getUsername, UserData::getAge, UserData::getSalary, UserData::getBirthDate)  // 手动选择需要查询的字段
                            .last("limit " + i + "," + BATCH_SIZE)
                            .list();  // 分页查询数据库
                    if (users.isEmpty()) {
                        break;  // 如果没有更多数据，则退出循环
                    }
                    excelWriter.write(users, writeSheet);
                    totalExported += users.size();
                    batchCount++;
                    log.info("批次 {}: 导出 {} 条数据，累计导出 {} 条数据，耗时: {} ms", batchCount, users.size(), totalExported, System.currentTimeMillis() - startTime);
                }
                excelWriter.finish();
            }
            log.info("总耗时: {} ms", System.currentTimeMillis() - startTime);
        } catch (Exception e) {
            log.error("导出 Excel 文件时发生错误: ", e);
        }
    }

    @Override
    public void exportExcelMan() {
        long startTime = System.currentTimeMillis();
        List<UserData> users = this.lambdaQuery()
                .select(UserData::getUsername, UserData::getAge, UserData::getSalary, UserData::getBirthDate)
                .list();
        File excelDir = new File("src/main/resources/excel");
        if (!excelDir.exists()) {
            excelDir.mkdirs();
        }
        File excelFile = new File(excelDir, "users.xlsx");
        try (FileOutputStream outputStream = new FileOutputStream(excelFile)) {
            // 2. 使用 EasyExcel 构建写入器
            try (ExcelWriter excelWriter = EasyExcel.write(outputStream, UserData.class).build()) {
                WriteSheet writeSheet = EasyExcel.writerSheet("用户数据").build();
                excelWriter.write(users, writeSheet);
                log.info("耗时: {} ms",System.currentTimeMillis() - startTime);
                excelWriter.finish();
            }
            log.info("慢导出结束，总耗时: {} ms", System.currentTimeMillis() - startTime);
        } catch (Exception e) {
            log.error("导出 Excel 文件时发生错误: ", e);
        }
    }

    @Override
    public void importExcel(MultipartFile file) {
        log.info("开始导入 Excel 文件到数据库");
        long startTime = System.currentTimeMillis();
        try (InputStream inputStream = file.getInputStream()) {
            EasyExcel.read(
                    inputStream,
                    UserData.class,
                    new ExcelUtil.UserDataListener(this, transactionManager)
            ).sheet().doRead();
            log.info("Excel 文件导入完成，总耗时: {} ms", System.currentTimeMillis() - startTime);
        } catch (Exception e) {
            log.error("导入 Excel 文件时发生错误: ", e);
        }
    }
}

